package com.itouchtv.yang.core.tools;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @program: webUIAutoTest
 * @description: 读取excel数据
 **/
public class AnalysisExcel {
    /**
     * 检查文件
     * @param path 文件目录
     * @return 返回Object二维数组
     */
    public static Object[][] read(String path) {
        File file = new File(path);
        if (!file.exists()) {
            Log.logger.error("文件不存在，请确认文件路径:" + path);
            Assert.fail("没有找到文件：" + path);
        } else if (path.endsWith("xls")) {
            return readExcel(path,"xls");
        } else if (path.endsWith("xlsx")) {
            return readExcel(path,"xlsx");
        } else Log.logger.error("无效的文件，只支持EXCEL");
        return null;
    }

    /**
     * 读取Excel表格数据
     * @param path 文件路径
     * @param format 文件后缀
     * @return Object[][]
     */
    private static Object[][] readExcel(String path,String format){
        List<List<Object>> rows = new ArrayList<>();
        List<Object> cells = new ArrayList<>();
        Workbook workbook = null;
        Sheet sheet;
        Row row;
        Cell cell;
        FormulaEvaluator formulaEvaluator;

        Log.logger.info("开始读取数据:" + path);
        format = format.toUpperCase();

        switch (format) {
            case "XLS":
                try {
                    workbook = new HSSFWorkbook(new FileInputStream(path));
                } catch (IOException e) {
                    e.printStackTrace();
                }
                break;
            case "XLSX":
                try {
                    workbook = new XSSFWorkbook(new FileInputStream(path));
                } catch (IOException e) {
                    e.printStackTrace();
                }
                break;
            default:
                Log.logger.error("无效的文件，只支持EXCEL");
                break;
        }

        Objects.requireNonNull(workbook,"读取EXCEL文件失败，workbook=null");
        formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        sheet = workbook.getSheetAt(0);
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) continue;
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (Objects.isNull(cell)){
                    cells.add("");
                    continue;
                }
                switch (cell.getCellTypeEnum()) {
                    case _NONE:
                        cells.add("");
                        break;
                    case BLANK:
                        cells.add("");
                        break;
                    case ERROR:
                        cells.add(cell.getErrorCellValue());
                        break;
                    case STRING:
                        cells.add(cell.getStringCellValue());
                        break;
                    case BOOLEAN:
                        cells.add(cell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        cells.add(formulaEvaluator.evaluate(cell).getNumberValue());
                        break;
                    case NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            cells.add(cell.getDateCellValue());
                        } else {
                            cell.setCellType(CellType.STRING);
                            cells.add(cell.getStringCellValue());
                        }
                }
            }
            rows.add(new ArrayList<>(cells));
            cells.clear();
        }
        return listToArray(rows);
    }

    /**
     * 把list转换成object二维数组
     * @param rows 要转换的list
     * @return Object[][]返回二维数据
     */
    private static Object[][] listToArray(List<List<Object>> rows) {
        //这里减1是为了去掉第一行标题数据
        Object[][] objects = new Object[rows.size() - 1][rows.get(0).size()];
        for (int i = 1; i < rows.size(); i++) {
            for (int j = 0; j < rows.get(i).size(); j++) {
                objects[i - 1][j] = rows.get(i).get(j);
            }
        }
        Log.logger.info("读取数据成功");
//        for (int i = 0; i < objects.length; i++) {
//            for (int j = 0; j < objects[i].length; j++) {
//                System.out.print(objects[i][j] + " ");
//            }
//            System.out.println("");
//        }
        return objects;
    }
}
